package com.kaizeli.website.service;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.ApplicationArguments;
import org.springframework.boot.ApplicationRunner;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;

/**
 * 数据库自动初始化服务
 * 在应用启动时自动执行数据库表结构更新
 */
@Service
public class DatabaseAutoInitService implements ApplicationRunner {

    private static final Logger log = LoggerFactory.getLogger(DatabaseAutoInitService.class);

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    @Transactional
    public void run(ApplicationArguments args) throws Exception {
        log.info("开始执行数据库自动初始化...");
        
        try {
            // 检查并创建验收相关表
            initAcceptanceTables();
            
            // 更新员工表结构
            updateEmployeeTable();
            
            log.info("数据库自动初始化完成");
        } catch (Exception e) {
            log.error("数据库自动初始化失败: {}", e.getMessage(), e);
            // 不抛出异常，避免影响应用启动
        }
    }

    /**
     * 初始化验收相关表
     */
    private void initAcceptanceTables() {
        try {
            // 检查表是否存在
            String checkTableSql = "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'kaizeli' AND table_name = 'project_acceptance'";
            Integer tableCount = jdbcTemplate.queryForObject(checkTableSql, Integer.class);
            
            if (tableCount != null && tableCount == 0) {
                log.info("验收表不存在，将通过JPA自动创建...");
                // JPA会自动创建表，这里只需要等待
            } else {
                log.info("验收表已存在，跳过创建");
            }
        } catch (Exception e) {
            log.warn("检查验收表时出现异常: {}", e.getMessage());
        }
    }

    /**
     * 更新员工表结构
     */
    private void updateEmployeeTable() {
        try {
            // 检查员工表是否存在新字段
            String checkColumnSql = "SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = 'kaizeli' AND table_name = 'employee' AND column_name = 'project_code'";
            Integer columnCount = jdbcTemplate.queryForObject(checkColumnSql, Integer.class);
            
            if (columnCount != null && columnCount == 0) {
                log.info("员工表缺少新字段，将通过JPA自动更新...");
                // JPA会自动添加新字段
            } else {
                log.info("员工表结构已是最新，跳过更新");
            }
        } catch (Exception e) {
            log.warn("检查员工表结构时出现异常: {}", e.getMessage());
        }
    }

    /**
     * 读取SQL脚本文件
     */
    private String readSqlScript(String scriptPath) throws IOException {
        ClassPathResource resource = new ClassPathResource(scriptPath);
        StringBuilder sqlScript = new StringBuilder();
        
        try (BufferedReader reader = new BufferedReader(
                new InputStreamReader(resource.getInputStream(), StandardCharsets.UTF_8))) {
            String line;
            while ((line = reader.readLine()) != null) {
                sqlScript.append(line).append("\n");
            }
        }
        
        return sqlScript.toString();
    }
}
